using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.Common;
using System.Data;
using Yogesh.ExcelXml;

/// <summary>
/// Imports from an Excel XML spreadsheet
/// </summary>
public partial class Import : System.Web.UI.Page
{
    private Database db;
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
    
    /// <summary>
    /// Uploads the file and begins importing.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnUpload_Click(object sender, EventArgs e) {
        if(uplXlsFile.HasFile) {
            List<string> errors = new List<string>();
            try {
                db = new Database();
                db.OpenConnection();
                List<string> uploadedAbbreviations = new List<string>();

                ExcelXmlWorkbook wb = ExcelXmlWorkbook.Import(uplXlsFile.FileContent);
                Worksheet sheet = wb["Languages"];

                if(sheet == null) {
                    throw new ArgumentException("Uploaded sheet does is not a valid workbook");
                }
                //todo: load meta from separate sheet
                int startingColumn = 1;
                for(int colIndex = startingColumn+1; colIndex < sheet.ColumnCount; colIndex++) { //  first two cells are padding/labels
                    uploadedAbbreviations.Add(sheet[colIndex, 0].Value.ToString());
                }

                using(Database.Command cmdCheckExisting = db.CreateCommand())
                using(Database.Command cmdInsertTranslation = db.CreateCommand())
                using(Database.Command cmdMaxRow = db.CreateCommand()) {
                    cmdCheckExisting.CommandText = "SELECT COUNT(translationid) FROM translations WHERE translationid = @tID";
                    cmdCheckExisting.AddParameterWithValue("@tID", "");
                    switch(db.InstanceType) {
                        case Database.DatabaseType.MySQL:
                            cmdInsertTranslation.CommandText = "INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans) ON DUPLICATE KEY UPDATE translation = @trans";
                            break;
                        case Database.DatabaseType.SQLServer:
                            cmdInsertTranslation.CommandText = "UPDATE translations SET translation = @trans WHERE translationid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO translations (translationid, language, translation) VALUES (@tID, @lang, @trans)";
                            break;
                    }
                    cmdInsertTranslation.AddParameterWithValue("@tID", "");
                    cmdInsertTranslation.AddParameterWithValue("@lang", "");
                    cmdInsertTranslation.AddParameterWithValue("@trans", "");
                    cmdMaxRow.CommandText = "SELECT MAX(translationid) FROM translations";
                    int maxId = 0;
                    
                    int translationsUpdated = 0;
                    for(int i = 1; i < sheet.RowCount; i++) {
                        try {
                            Row row = sheet[i];
                            int translationId = 0;
                            if(!int.TryParse(row[startingColumn].Value.ToString(), out translationId)) {
                                using(DbDataReader maxIdReader = cmdMaxRow.ExecuteReader(CommandBehavior.SingleRow)) {
                                    if(maxIdReader.HasRows) {
                                        maxIdReader.Read();
                                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                                        translationId++;
                                    }
                                    maxIdReader.Close();
                                }
                            }
                            cmdCheckExisting.Parameters["@tID"].Value = translationId;
                            for(int j = 0; j < row.CellCount - startingColumn - 1; j++) { // add each language to the translations table
                                string langAbbrev = uploadedAbbreviations[j];
                                string translation = row[j + startingColumn + 1].Value == null ? null : row[j + startingColumn + 1].Value.ToString();
                                if(translation != null && translation.Length > 0) {
                                    cmdInsertTranslation.Parameters["@tID"].Value = translationId;
                                    cmdInsertTranslation.Parameters["@lang"].Value = langAbbrev;
                                    cmdInsertTranslation.Parameters["@trans"].Value = HttpUtility.HtmlDecode(translation);
                                    cmdInsertTranslation.ExecuteNonQuery();
                                    translationsUpdated++;
                                }
                            }
                        } catch {
                            errors.Add("Error reading row "+i);
                        }
                        // TODO: fix deleting translation without deleting element
                        // TODO: add new translation by appending rows to spreadsheet (read max id etc.)
                    }
                    resultDiv.Visible = true;
                    resultDiv.InnerText = "Added " + translationsUpdated + " elements";
                }
            } catch(Exception ex) {
                errors.Add(ex.Message);
            }
            if(errors.Count > 0) {
                errorDiv.Visible = true;
                errorDiv.InnerHtml += "<ul>";
                foreach(string err in errors) {
                    errorDiv.InnerHtml += "<li>"+err+"</li>";
                }
                errorDiv.InnerHtml += "</ul>";
            }
        }
    }
}
